﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DAO
{
    public class PinDAO
    {
/*-------------------------LOAD----------------------------------------------------
LOADPIN
LOADPIN_THEOSTT @STT_Pin int
LOADPIN_THEOMATHONGSO @MaThongSo int
LOADPIN_THEOLOAI @LoaiPin nvarchar(10)
 */
        
        public DataTable LoadPin()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADPIN",_cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            _cn.Close();
            return _dt;
        }
        public DataTable LoadPin_TheoSTT(int STT_Pin)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADPIN_THEOSTT",_cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@STT_Pin",SqlDbType.Int).Value=STT_Pin;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            _cn.Close();
            return _dt;
        }
        public DataTable LoadPin_TheoMaThongSo(int MaThongSo)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADPIN_THEOMATHONGSO",_cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@MaThongSo",SqlDbType.Int).Value=MaThongSo;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            _cn.Close();
            return _dt;
        }
        public DataTable LoadPin_TheoLoai(int LoaiPin)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADPIN_THEOLOAI",_cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@LoaiPin",SqlDbType.Int).Value=LoaiPin;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            _cn.Close();
            return _dt;
        }
/*-------------------------INSERT----------------------------------------------------
THEMPIN @STT_Pin int out, @LoaiPin nvarchar(10),@VMin decimal@VMax decimal,@AMin decimal, @AMax decimal, @MaThongSo int
*/
        public int ThemPin(PinDTO pin)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMPIN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.Date));
            cmd.Parameters.Add(new SqlParameter("@VMin", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@VMax", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@AMin", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@AMax", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@KichThuoc", SqlDbType.VarChar, 15));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaLoaiPin", SqlDbType.Int));
            cmd.Parameters["@MaTB"].Value = pin.MATB;
            cmd.Parameters["@TenTB"].Value = pin.TENTB;
            cmd.Parameters["@NgayNhap"].Value = pin.NGAYNHAP;
            cmd.Parameters["@VMin"].Value = pin.VMIN;
            cmd.Parameters["@VMax"].Value = pin.VMAX;
            cmd.Parameters["@AMin"].Value = pin.AMIN;
            cmd.Parameters["@AMax"].Value = pin.AMAX;
            cmd.Parameters["@KichThuoc"].Value = pin.KICHTHUOC;
            cmd.Parameters["@CSKhaDung"].Value = pin.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = pin.DONGIA;
            cmd.Parameters["@MaLoaiPin"].Value = pin.MALOAIPIN;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
/*-------------------------UPDATE----------------------------------------------------
SUAPIN @STT_Pin int, @LoaiPin nvarchar(10), @VMin decimal,@VMax decimal,@AMin decimal,@AMax decimal
*/
        public int SuaPin(PinDTO pin)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SUAPIN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT_Pin", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@VMin", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@VMax", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@AMin", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@AMax", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@KichThuoc", SqlDbType.VarChar, 15));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaLoaiPin", SqlDbType.Int));
            cmd.Parameters["@STT_Pin"].Value = pin.STT_PIN;
            cmd.Parameters["@MaTB"].Value = pin.MATB;
            cmd.Parameters["@TenTB"].Value = pin.TENTB;
            cmd.Parameters["@VMin"].Value = pin.VMIN;
            cmd.Parameters["@VMax"].Value = pin.VMAX;
            cmd.Parameters["@AMin"].Value = pin.AMIN;
            cmd.Parameters["@AMax"].Value = pin.AMAX;
            cmd.Parameters["@CSKhaDung"].Value = pin.CSKHADUNG;
            cmd.Parameters["@KichThuoc"].Value = pin.KICHTHUOC;
            cmd.Parameters["@DonGia"].Value = pin.DONGIA;
            cmd.Parameters["@MaLoaiPin"].Value = pin.MALOAIPIN;
            int _kq=cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
/*-------------------------DELETE----------------------------------------------------
XOAPIN @STT_Pin int
*/
        public int XoaPin(int STT_Pin)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XOAPIN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT_Pin", SqlDbType.Int));
            cmd.Parameters["@STT_Pin"].Value = STT_Pin;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
    }
}
